import streamlit as st
import sqlite3
from datetime import datetime

# 数据库连接
def get_db_connection():
    conn = sqlite3.connect('blog.db')
    conn.row_factory = sqlite3.Row  # 以字典形式返回结果
    return conn

# 初始化数据库表
def init_db():
    conn = get_db_connection()
    conn.execute('''
        CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    conn.commit()
    conn.close()

# 插入文章
def insert_post(title, content):
    conn = get_db_connection()
    conn.execute('INSERT INTO posts (title, content) VALUES (?, ?)', (title, content))
    conn.commit()
    conn.close()

# 获取所有文章
def get_all_posts():
    conn = get_db_connection()
    posts = conn.execute('SELECT * FROM posts ORDER BY created_at DESC').fetchall()
    conn.close()
    return posts

# 获取单篇文章
def get_post_by_id(post_id):
    conn = get_db_connection()
    post = conn.execute('SELECT * FROM posts WHERE id = ?', (post_id,)).fetchone()
    conn.close()
    return post

# 初始化数据库
init_db()

# Streamlit 应用
st.title("我的博客系统")

# 侧边栏：录入文章
with st.sidebar:
    st.header("录入新文章")
    title = st.text_input("标题")
    content = st.text_area("内容（Markdown 格式）")
    if st.button("发布"):
        if title and content:
            insert_post(title, content)
            st.success("文章发布成功！")
        else:
            st.error("标题和内容不能为空")

# 主界面：展示文章
st.header("文章列表")
posts = get_all_posts()
if posts:
    for post in posts:
        with st.expander(post['title']):
            st.markdown(post['content'])
            st.caption(f"发布时间：{post['created_at']}")
else:
    st.info("暂无文章，快去发布一篇吧！")